package com.mavenq.fly.utils.excel;

import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.Closeable;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel 报表通用工具类
 * 支持 xls 和 xlsx 格式的读写操作
 */
public class ExcelUtil {

    private static final String XLSX_SUFFIX = ".xlsx";
    private static final String XLS_SUFFIX = ".xls";

    // 默认日期格式
    private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";

    // 默认列宽（字符数）
    private static final int DEFAULT_COLUMN_WIDTH = 15;
    // 最小列宽
    private static final int MIN_COLUMN_WIDTH = 8;
    // 最大列宽
    private static final int MAX_COLUMN_WIDTH = 50;
    // 默认行高（点数）
    private static final short DEFAULT_ROW_HEIGHT = 400;
    // 表头行高
    private static final short HEADER_ROW_HEIGHT = 500;

    /**
     * 导出数据到 Excel
     */
    public static <T> void exportToExcel(List<T> dataList, String[] headers, String[] fieldNames,
                                         String sheetName, OutputStream outputStream, boolean isXlsx) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");

            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length);

            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);

            // 创建表头行（设置更高的行高）
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(HEADER_ROW_HEIGHT);

            // 创建表头单元格
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(DEFAULT_ROW_HEIGHT);
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }

            // 自适应列宽（在数据填充后调用）
            autoSizeColumns(sheet, headers.length, dataList, fieldNames);

            workbook.write(outputStream);

        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }

    /**
     * 导出数据到 Excel（高级版，支持更多自定义选项）
     */
    public static <T> void exportToExcelAdvanced(List<T> dataList, String[] headers, String[] fieldNames,
                                                 String sheetName, OutputStream outputStream,
                                                 boolean isXlsx, ExcelExportConfig config) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");

            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length, config.getDefaultColumnWidth());

            // 创建样式
            CellStyle headerStyle = createCustomHeaderStyle(workbook, config);
            CellStyle dataStyle = createCustomDataStyle(workbook, config);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(config.getHeaderRowHeight());

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(config.getDataRowHeight());
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }

            // 根据配置决定是否自适应列宽
            if (config.isAutoSizeColumns()) {
                autoSizeColumns(sheet, headers.length, dataList, fieldNames,
                        config.getMinColumnWidth(), config.getMaxColumnWidth());
            }

            workbook.write(outputStream);

        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }

    /**
     * 设置默认列宽
     */
    private static void setDefaultColumnWidth(Sheet sheet, int columnCount) {
        setDefaultColumnWidth(sheet, columnCount, DEFAULT_COLUMN_WIDTH);
    }

    private static void setDefaultColumnWidth(Sheet sheet, int columnCount, int width) {
        for (int i = 0; i < columnCount; i++) {
            sheet.setColumnWidth(i, width * 256); // POI中1个字符宽度=256
        }
    }

    /**
     * 自适应列宽（智能调整）
     */
    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList, String[] fieldNames) {
        autoSizeColumns(sheet, columnCount, dataList, fieldNames, MIN_COLUMN_WIDTH, MAX_COLUMN_WIDTH);
    }

    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList,
                                            String[] fieldNames, int minWidth, int maxWidth) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);

            // 获取自动调整后的列宽
            int currentWidth = sheet.getColumnWidth(i);
            int charWidth = currentWidth / 256;

            // 应用最小和最大宽度限制
            if (charWidth < minWidth) {
                sheet.setColumnWidth(i, minWidth * 256);
            } else if (charWidth > maxWidth) {
                sheet.setColumnWidth(i, maxWidth * 256);
            }

            // 额外增加一些边距，让内容显示更舒适
            int adjustedWidth = sheet.getColumnWidth(i);
            sheet.setColumnWidth(i, adjustedWidth + 512); // 增加2个字符的边距
        }
    }

    /**
     * 创建优化的表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置背景色
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框（更粗的边框）
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);

        // 设置字体（更大更粗）
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        // 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置自动换行
        style.setWrapText(true);

        return style;
    }

    /**
     * 创建优化的数据行样式
     */
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置自动换行
        style.setWrapText(true);

        // 创建数据格式
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@")); // 文本格式

        return style;
    }

    /**
     * 创建自定义表头样式
     */
    private static CellStyle createCustomHeaderStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();

        if (config.getHeaderBgColor() != null) {
            style.setFillForegroundColor(config.getHeaderBgColor().getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        style.setBorderBottom(config.getHeaderBorderStyle());
        style.setBorderTop(config.getHeaderBorderStyle());
        style.setBorderLeft(config.getHeaderBorderStyle());
        style.setBorderRight(config.getHeaderBorderStyle());

        Font font = workbook.createFont();
        font.setBold(config.isHeaderBold());
        font.setFontHeightInPoints(config.getHeaderFontSize());
        if (config.getHeaderFontColor() != null) {
            font.setColor(config.getHeaderFontColor().getIndex());
        }
        style.setFont(font);

        style.setAlignment(config.getHeaderAlignment());
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(config.isWrapText());

        return style;
    }

    /**
     * 创建自定义数据行样式
     */
    private static CellStyle createCustomDataStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();

        style.setBorderBottom(config.getDataBorderStyle());
        style.setBorderTop(config.getDataBorderStyle());
        style.setBorderLeft(config.getDataBorderStyle());
        style.setBorderRight(config.getDataBorderStyle());

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        if (config.getDataAlignment() != null) {
            style.setAlignment(config.getDataAlignment());
        }

        style.setWrapText(config.isWrapText());

        return style;
    }

    /**
     * 从 Excel 导入数据
     *
     * @param inputStream Excel 输入流
     * @param headers     表头数组（用于验证）
     * @param fieldNames  字段名数组
     * @param clazz       目标类类型
     * @param isXlsx      是否为 xlsx 格式
     */
    public static <T> List<T> importFromExcel(InputStream inputStream, String[] headers,
                                              String[] fieldNames, Class<T> clazz, boolean isXlsx) {
        List<T> resultList = new ArrayList<>();
        Workbook workbook = null;

        try {
            workbook = isXlsx ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 验证表头
            if (!validateHeader(sheet, headers)) {
                throw new RuntimeException("Excel表头不匹配");
            }

            // 读取数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                T obj = clazz.newInstance();
                boolean hasData = false;

                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        String fieldName = fieldNames[j];
                        Object value = getCellValue(cell);
                        setFieldValue(obj, fieldName, value);
                        hasData = true;
                    }
                }

                if (hasData) {
                    resultList.add(obj);
                }
            }

        } catch (Exception e) {
            throw new RuntimeException("导入Excel失败", e);
        } finally {
            closeWorkbook(workbook);
            closeStream(inputStream);
        }

        return resultList;
    }


    /**
     * 填充行数据（优化版，处理长文本）
     */
    private static <T> void fillRowData(Row row, T data, String[] fieldNames, CellStyle style) {
        try {
            for (int i = 0; i < fieldNames.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);

                String fieldName = fieldNames[i];
                Object value = getFieldValue(data, fieldName);
                setCellValue(cell, value);
            }
        } catch (Exception e) {
            throw new RuntimeException("填充行数据失败", e);
        }
    }

    /**
     * 获取字段值
     */
    private static Object getFieldValue(Object obj, String fieldName) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return "";
            
            field.setAccessible(true);
            return field.get(obj);
        } catch (Exception e) {
            return "";
        }
    }

    /**
     * 设置字段值
     */
    private static void setFieldValue(Object obj, String fieldName, Object value) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return;
            
            field.setAccessible(true);
            Class<?> fieldType = field.getType();
            
            if (value == null) {
                field.set(obj, null);
            } else if (fieldType == String.class) {
                field.set(obj, value.toString());
            } else if (fieldType == Integer.class || fieldType == int.class) {
                field.set(obj, Integer.parseInt(value.toString()));
            } else if (fieldType == Long.class || fieldType == long.class) {
                field.set(obj, Long.parseLong(value.toString()));
            } else if (fieldType == Double.class || fieldType == double.class) {
                field.set(obj, Double.parseDouble(value.toString()));
            } else if (fieldType == BigDecimal.class) {
                field.set(obj, new BigDecimal(value.toString()));
            } else if (fieldType == Date.class) {
                if (value instanceof Date) {
                    field.set(obj, value);
                } else {
                    // 尝试解析日期字符串
                    SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_FORMAT);
                    field.set(obj, sdf.parse(value.toString()));
                }
            } else if (fieldType == Boolean.class || fieldType == boolean.class) {
                field.set(obj, Boolean.parseBoolean(value.toString()));
            }
        } catch (Exception e) {
            // 忽略字段设置异常
        }
    }

    /**
     * 获取字段（包括父类字段）
     */
    private static Field getField(Class<?> clazz, String fieldName) {
        for (Class<?> superClass = clazz; superClass != Object.class; superClass = superClass.getSuperclass()) {
            try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
                // 继续在父类中查找
            }
        }
        return null;
    }

    /**
     * 获取单元格值
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 设置单元格值
     */
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            if (value instanceof Integer || value instanceof Long || 
                value instanceof Double || value instanceof Float) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).doubleValue());
            }
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 验证表头
     */
    private static boolean validateHeader(Sheet sheet, String[] headers) {
        if (headers == null || headers.length == 0) return true;
        
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) return false;
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null || !headers[i].equals(cell.getStringCellValue())) {
                return false;
            }
        }
        
        return true;
    }

    /**
     * 关闭工作簿
     */
    private static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }

    /**
     * 关闭流
     */
    private static void closeStream(Closeable stream) {
        if (stream != null) {
            try {
                stream.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }

    /**
     * 合并单元格
     */
    public static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }


    /**
     * Excel 样式配置类
     */
    @Data
    public static class ExcelExportConfig {
        // 样式配置
        private IndexedColors headerBgColor = IndexedColors.SKY_BLUE;
        private IndexedColors headerFontColor = IndexedColors.WHITE;
        private short headerFontSize = 14;
        private boolean headerBold = true;
        private HorizontalAlignment headerAlignment = HorizontalAlignment.CENTER;
        private HorizontalAlignment dataAlignment = HorizontalAlignment.LEFT;
        private BorderStyle headerBorderStyle = BorderStyle.MEDIUM;
        private BorderStyle dataBorderStyle = BorderStyle.THIN;
        private boolean wrapText = true;

        // 尺寸配置
        private short headerRowHeight = 500;
        private short dataRowHeight = 400;
        private int defaultColumnWidth = 15;
        private int minColumnWidth = 8;
        private int maxColumnWidth = 50;
        private boolean autoSizeColumns = true;
    }
}

